﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ES.Client;
using Nest;

using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;

using Newtonsoft.Json.Linq;

namespace ES.Client.Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            //ContactDailyIncreasementReport();
            EventSearch();
            //SearchArticles();
            //AssociatorSearch();
            //AggregationTest();
            //AggregationTest1();
            //AggregationTest2();
            //ContactDailyIncreasementReport();
            Console.ReadKey();
        }


        static void SearchJoins()
        {
            ElasticHub<HYEventJoin> hub = new ElasticHub<HYEventJoin>("hexindb_eventjoins");
            var parameter = hub.NewParameter();
            //"Int_Field648413083": 55,
            //parameter.Must.EQ("ExtraFieldsForES.Int_Field648413083", 55);
            parameter.Must.IN("ExtraFieldsForES.String_Field648413079", new List<string>() { "多选3" });
            var response = hub.Search(parameter);
            var docs = response.Documents;
        }


        static void SearchArticles()
        {
            ElasticHub<Article> hub = new ElasticHub<Article>("scrmdb_microsites_articles");
            var parameter = hub.NewParameter();
            //parameter.Highlighter.Fields(f => f.Field(new Field("*")).PreTags("<baidu>").PostTags("</baidu>"));
            parameter.Must.IsNullOrEmpty("Name");
            //parameter.Source.Includes(t => t.Fields(f => f.Content, f => f.Description));
            //parameter.Source.Excludes(t => t.Fields(f => f.Content, f => f.Description));
            parameter.Sorter.Descending(t => t.ArticleId);
            var response = hub.Search(parameter);
            if (response.IsValid)
            {
                var documents = response.GetHighlightedDocuments();
                foreach (var doc in documents)
                {
                    Console.WriteLine(doc.Content + "-------------" + doc.Description);
                }
            }
        }

        static void EventSearch()
        {
            var keyword = "姚世荣";
            var elastic = new ElasticHub<HYEvent>("hexindb");
            var parameter = elastic.NewParameter();
            parameter.Must.EQ("IsDeleted", false).IsNullOrEmpty("Title");
            parameter.Sorter.Descending(t => t.SubType);
            parameter.Size = 10000;
            parameter.From = 0;
            Console.WriteLine("------------------start--------------------");
            var response = elastic.Search(parameter);
            if (response.IsValid)
            {
                Console.WriteLine(response.Total);
                var data = response.Documents.ToList();
                foreach (var item in data)
                {
                    Console.WriteLine(item.Title);
                }
            }
            Console.WriteLine("------------------finish--------------------");
        }

        static void AssociatorSearch()
        {
            var elastic = new ElasticHub<Associator>();
            var parameter = elastic.NewParameter();
            parameter.Highlighter.Fields(
                f => f.Field(a => a.RealName).PreTags("<RealName>").PostTags("</RealName>"),
                f => f.Field(a => a.Company).PreTags("<Company>").PostTags("</Company>"));
            parameter.Must.EQ("IsDeleted", false).FullText("王");
            parameter.Should.Like(a => a.RealName, "王").Like(a => a.Company, "31会");
            parameter.Size = 100;

            var response = elastic.Search(parameter);
            if (response.IsValid)
            {
                var data = response.GetHighlightedDocuments();
                foreach (var item in data)
                {
                    Console.WriteLine(item.AssociatorId + "----" + item.RealName + "---" + item.Company);
                }
            }

            Console.WriteLine("finish");
        }

        static void ContactDailyIncreasementReport()
        {
            ElasticHub<Contact> hub = new ElasticHub<Contact>("scrmdb");
            QueryParameter<Contact> parameter = hub.NewParameter();
            parameter.Must.EQ("CompanyId", 281111784);
            parameter.Must.FullText("测试");
            parameter.Size = 1000000;
            var response = hub.Search(parameter);
            if (response.IsValid)
            {
                var terms = response.Documents;
                //foreach (var item in terms)
                //{
                //    Console.WriteLine(item.Key + "----" + item.DocCount);
                //}
            }
        }

        static void MapContact()
        {
            var elastic = new ElasticHub<Contact>("scrmdb0012121212121");
            var scrmdbResponse = elastic.Client.CreateIndex(elastic.Index, c => c.Mappings(s =>
                s.Map<Contact>(dd => dd.AutoMap().DateDetection(false).DynamicTemplates(t => t.
                    DynamicTemplate("ExtraFieldsForES_Int", td => td.PathMatch("ExtraFieldsForES.Int_*").Mapping(m => m.Number(p => p.Type(NumberType.Integer).IgnoreMalformed(true)))).
                    DynamicTemplate("ExtraFieldsForES_Double", td => td.PathMatch("ExtraFieldsForES.Double_*").Mapping(m => m.Number(p => p.Type(NumberType.Double).IgnoreMalformed(true)))).
                    DynamicTemplate("ExtraFieldsForES_Bool", td => td.PathMatch("ExtraFieldsForES.Bool_*").Mapping(m => m.Boolean(p => p.Index(true)))).
                    DynamicTemplate("ExtraFieldsForES_String", td => td.PathMatch("ExtraFieldsForES.String_*").Mapping(m => m.Keyword(p => p.Index(true)))).
                    DynamicTemplate("ExtraFieldsForES_Date", td => td.PathMatch("ExtraFieldsForES.Date_*").Mapping(m => m.Date(p => p.Format("yyyy-MM-dd'T'HH:mm:ss||yyyy-MM-dd HH:mm:ss||yyyy/MM/dd HH:mm:ss||strict_date_optional_time").Index(true).IgnoreMalformed(true))))
                    ))));

            var scrmdb = scrmdbResponse.DebugInformation;
        }

        static void AnalyzeTest()
        {
            var elastic = new ElasticHub<HYEvent>("hexindb");
            var tokens = elastic.AnalyzeByField("titl", "飞来横福啥都会1213asas防守反击");
        }

        static void SearchTest0()
        {
            var elastic = new ElasticHub<Contact>();
            var parameter = elastic.NewParameter();
            parameter.Must.EQ("CompanyId", 355769313);
            //parameter.Must.EQ("IsDeleted", false);
            parameter.Must.Like(c => c.Company, "ANSYS");
            //parameter.Must.EQ(c => c.RealName, "肖骁");
            //parameter.Must.OR(
            //    parameter.GetBuilder().Like(c => c.City, "ddddd").Build()
            //    ).OR(
            //    parameter.GetBuilder().Like(c => c.Address, "").Build()
            //    );

            parameter.Size = 100;

            var response = elastic.Search(parameter);
            if (response.IsValid)
            {
                var total = response.Total;
                //Console.WriteLine(total);
                var contacts = response.Documents;
                //var ss = contacts.Count();
                foreach (var item in contacts)
                {
                    Console.WriteLine(item.RealName + "---" + item.Company);
                }
            }

            Console.WriteLine("finish");
        }

        static void SearchTest()
        {
            var dt0 = DateTime.Now;

            var elastic = new ElasticHub<Contact>();
            var parameter = elastic.NewParameter();
            var conditionMust = parameter.Must;
            conditionMust.EQ("CompanyId", 104375386);
            conditionMust.EQ("IsDeleted", false);
            conditionMust.EQ(c => c.RealName, "肖骁");
            parameter.Size = 100;

            //var elas = new ES.Api.ElasticHub<Contact>("scrmdb_contacts_contact");
            //var para = new ES.Api.QueryParameter<Contact>();
            //var con = new ES.Api.QueryCondition<Contact>("scrmdb_contacts_contact");
            //con.EQ("CompanyId", 104375386);
            //con.EQ("IsDeleted", false);
            //con.EQ("RealName", "肖骁");
            //para.AddMust(con.Build());
            //para.Size = 100;
            //var response = elas.Search(para);

            var response = elastic.Search(parameter);
            var ts = response.Took;
            var total = response.Total;
            var dt1 = DateTime.Now;
            var seconds = (dt1 - dt0).TotalSeconds;
            if (response.IsValid)
            {
                var contacts = response.Documents;
                var ss = contacts.Count();
                foreach (var item in contacts)
                {
                    Console.WriteLine(item.RealName + "---" + item.Email);
                }
            }

            Console.WriteLine("耗时：" + ts + "毫秒");
            Console.WriteLine("finish");
        }

        static void AggregationTest()
        {
            ElasticHub<HYEvent> hub = new ElasticHub<HYEvent>("hexindb");

            var parameter = hub.NewParameter();
            parameter.Must.GTE("DateFrom", new DateTime(2016, 1, 1, 0, 0, 0));


            //var termsResponse = hub.Terms(parameter, "EventSubType", "-100", 100, new TermsOrder() { Key = "_term", Order = SortOrder.Descending }, 15);
            //var ss0000 = "";

            var response = hub.Client.Search<HYEvent>(search => search.Aggregations(agg => agg.Filter("thisyear", t => t.
                Filter(filter => filter.
                       Bool(bs => bs.
                               Must(parameter.Must.Build()).
                               MustNot(parameter.MustNot.Build()).
                               Should(parameter.Should.Build())
                     )
             ).Aggregations(af => af.Terms("EventSubType", st => st.Field("EventSubType").Size(100).
                      Aggregations(
                              a1 => a1.Sum("JoinCount", a1f => a1f.Field("JoinCount")).
                              Range("Days", a1r => a1r.Script("doc['DateTo'].value-doc['DateFrom'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(43200001).Key("0.5天"),
                                            r => r.From(43200001).To(86400001).Key("1天"),
                                            r => r.From(86400001).To(172800001).Key("2天"),
                                            r => r.From(172800001).Key("3天或以上")
                              }.ToArray())).
                              Range("Month", a2r => a2r.Script("new Date(doc['DateFrom'].value).getMonth()").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.From(0).To(1).Key("1月"),
                                            r => r.From(1).To(2).Key("2月"),
                                            r => r.From(2).To(3).Key("3月"),
                                            r => r.From(3).To(4).Key("4月"),
                                            r => r.From(4).To(5).Key("5月"),
                                            r => r.From(5).To(6).Key("6月"),
                                            r => r.From(6).To(7).Key("7月"),
                                            r => r.From(7).To(8).Key("8月"),
                                            r => r.From(8).To(9).Key("9月"),
                                            r => r.From(9).To(10).Key("10月"),
                                            r => r.From(10).To(11).Key("11月"),
                                            r => r.From(11).To(12).Key("12月"),
                              }.ToArray())).
                              Range("Scale", a2r => a2r.Script("doc['JoinCount'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(50).Key("0-49"),
                                            r => r.From(50).To(100).Key("50-99"),
                                            r => r.From(100).To(200).Key("100-199"),
                                            r => r.From(200).To(500).Key("200-499"),
                                            r => r.From(500).To(1000).Key("500-999"),
                                            r => r.From(1000).Key("1000或以上")
                              }.ToArray()))
                  ))))));
            var debug = response.DebugInformation;
            var a = response.Aggregations.ToList()[0].Value as Nest.SingleBucketAggregate;
            var bucketAgg = a.Aggregations.ToList()[0].Value as Nest.BucketAggregate;
            var keyedBucketsAgg = bucketAgg.Items.Cast<Nest.KeyedBucket<string>>().ToList();

            List<JObject> data = new List<JObject>();
            foreach (var keyedBucket in keyedBucketsAgg)
            {
                var keyedBucket_Key = keyedBucket.Key;
                var keyedBucket_Count = keyedBucket.DocCount;

                JObject dy = new JObject();
                var desc = Enum.Parse(typeof(EventSubType), keyedBucket_Key).ToString();
                dy.Add("会议类型", desc);
                dy.Add("会议数量", keyedBucket_Count);

                foreach (var subKey in keyedBucket.Aggregations.Keys)
                {
                    switch (subKey)
                    {
                        case "JoinCount":
                            {
                                Nest.ValueAggregate joinCountAgg = keyedBucket.Aggregations["JoinCount"] as Nest.ValueAggregate;
                                if (joinCountAgg != null)
                                {
                                    dy["参会人数"] = joinCountAgg.Value;
                                }
                            }
                            break;
                        case "Days":
                            {
                                var daysAgg = keyedBucket.Aggregations["Days"] as Nest.BucketAggregate;
                                var dayItems = daysAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in dayItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Month":
                            {
                                var monthAgg = keyedBucket.Aggregations["Month"] as Nest.BucketAggregate;
                                var monthItems = monthAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in monthItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Scale":
                            {
                                var scaleAgg = keyedBucket.Aggregations["Scale"] as Nest.BucketAggregate;
                                var scaleItems = scaleAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in scaleItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                    }
                }

                data.Add(dy);
            }


            var json = Newtonsoft.Json.JsonConvert.SerializeObject(data);
            var ss = Newtonsoft.Json.JsonConvert.DeserializeObject<List<JObject>>(json);



            using (ExcelPackage package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("sheet1");
                var colums = data[0].Properties().ToList();

                for (int j = 1; j <= colums.Count; j++)
                {
                    worksheet.Cells[1, j].Style.Fill.PatternType = ExcelFillStyle.Gray125;
                    //worksheet.Cells[1, j].Style.ShrinkToFit = true;
                    worksheet.Cells[1, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells[1, j].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
                    worksheet.Cells[1, j].Style.Font.Size = 16;
                    worksheet.Cells[1, j].Style.Font.Bold = true;
                    worksheet.Cells[1, j].Value = colums[j - 1].Name;
                }

                for (int i = 1; i <= data.Count; i++)
                {
                    var row = data[i - 1];

                    for (int j = 1; j <= colums.Count; j++)
                    {
                        worksheet.Cells[i + 1, j].Value = row[colums[j - 1].Name].ToString();
                    }
                }



                if (File.Exists("d:\\456.xlsx"))
                {
                    File.Delete("d:\\456.xlsx");
                }

                using (FileStream stream = new FileStream("d:\\456.xlsx", FileMode.Create, FileAccess.ReadWrite))
                {
                    package.SaveAs(stream);
                }
            }
        }

        static void AggregationTest2()
        {
            ElasticHub<HYEvent> hub = new ElasticHub<HYEvent>("hexindb");
            var parameter = hub.NewParameter();
            parameter.Must.GTE("DateFrom", new DateTime(2016, 1, 1, 0, 0, 0)).Like("World", "中国");
            parameter.MustNot.IsNullOrEmpty("Province").
                Like("Province", "北京").Like("Province", "北京市").Like("Province", "beijing").
                Like("Province", "上海").Like("Province", "上海市").Like("Province", "shanghai").
                Like("Province", "天津").Like("Province", "天津市").Like("Province", "tianjin").
                Like("Province", "重庆").Like("Province", "重庆市").Like("Province", "chongqing").
                Like("Province", "欧洲");

            var parameter1 = hub.NewParameter();
            parameter1.Must.GTE("DateFrom", new DateTime(2016, 1, 1, 0, 0, 0)).Like("World", "中国").IsNotNullAndEmpty("Province");
            parameter1.Must.IN("Province", new List<string>() {
                "北京","beijing","上海","shanghai","天津","tianjin","重庆","chongqing"
            });

            var response = hub.Client.Search<HYEvent>(search => search.Aggregations(agg => agg.
            Filter("thisyear", t => t.Filter(f => f.Bool(b => b.Must(parameter.Must.Build()).MustNot(parameter.MustNot.Build()))).
                Aggregations(af => af.Terms("City", st => st.Script("_source['City']").Exclude(new List<string>() {
                    "1",
                    "-1",
                    "alashanmeng",
                    "aomen",
                    "guangzhou",
                    "taizhou",
                    "xiamen",
                    "xian",
                    "tangshan",
                    "suzhou",
                    ""
                }).Missing("其他").Size(10000).
                      Aggregations(
                              a1 => a1.Sum("JoinCount", a1f => a1f.Field("JoinCount")).
                              Range("Days", a1r => a1r.Script("doc['DateTo'].value-doc['DateFrom'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(43200001).Key("0.5天"),
                                            r => r.From(43200001).To(86400001).Key("1天"),
                                            r => r.From(86400001).To(172800001).Key("2天"),
                                            r => r.From(172800001).Key("3天或以上")
                              }.ToArray())).
                              Range("Month", a2r => a2r.Script("new Date(doc['DateFrom'].value).getMonth()").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.From(0).To(1).Key("1月"),
                                            r => r.From(1).To(2).Key("2月"),
                                            r => r.From(2).To(3).Key("3月"),
                                            r => r.From(3).To(4).Key("4月"),
                                            r => r.From(4).To(5).Key("5月"),
                                            r => r.From(5).To(6).Key("6月"),
                                            r => r.From(6).To(7).Key("7月"),
                                            r => r.From(7).To(8).Key("8月"),
                                            r => r.From(8).To(9).Key("9月"),
                                            r => r.From(9).To(10).Key("10月"),
                                            r => r.From(10).To(11).Key("11月"),
                                            r => r.From(11).To(12).Key("12月"),
                              }.ToArray())).
                              Range("Scale", a2r => a2r.Script("doc['JoinCount'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(50).Key("0-49"),
                                            r => r.From(50).To(100).Key("50-99"),
                                            r => r.From(100).To(200).Key("100-199"),
                                            r => r.From(200).To(500).Key("200-499"),
                                            r => r.From(500).To(1000).Key("500-999"),
                                            r => r.From(1000).Key("1000或以上")
                              }.ToArray()))
                  )))).Filter("thisyear1", t => t.Filter(f => f.Bool(b => b.Must(parameter1.Must.Build()).MustNot(parameter1.MustNot.Build()))).
                Aggregations(af => af.Terms("City", st => st.Field("Province").Exclude(new List<string>() {
                    "1",
                    "-1",
                    "alashanmeng",
                    "aomen",
                    "guangzhou",
                    "taizhou",
                    "xiamen",
                    "xian",
                    "tangshan",
                    "suzhou",
                    "",
                    "shanghai",
                    "beijing"
                }).Missing("其他").Size(10000).
                      Aggregations(
                              a1 => a1.Sum("JoinCount", a1f => a1f.Field("JoinCount")).
                              Range("Days", a1r => a1r.Script("doc['DateTo'].value-doc['DateFrom'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(43200001).Key("0.5天"),
                                            r => r.From(43200001).To(86400001).Key("1天"),
                                            r => r.From(86400001).To(172800001).Key("2天"),
                                            r => r.From(172800001).Key("3天或以上")
                              }.ToArray())).
                              Range("Month", a2r => a2r.Script("new Date(doc['DateFrom'].value).getMonth()").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.From(0).To(1).Key("1月"),
                                            r => r.From(1).To(2).Key("2月"),
                                            r => r.From(2).To(3).Key("3月"),
                                            r => r.From(3).To(4).Key("4月"),
                                            r => r.From(4).To(5).Key("5月"),
                                            r => r.From(5).To(6).Key("6月"),
                                            r => r.From(6).To(7).Key("7月"),
                                            r => r.From(7).To(8).Key("8月"),
                                            r => r.From(8).To(9).Key("9月"),
                                            r => r.From(9).To(10).Key("10月"),
                                            r => r.From(10).To(11).Key("11月"),
                                            r => r.From(11).To(12).Key("12月"),
                              }.ToArray())).
                              Range("Scale", a2r => a2r.Script("doc['JoinCount'].value").Ranges(new List<Func<RangeDescriptor, IRange>>() {
                                            r => r.To(50).Key("0-49"),
                                            r => r.From(50).To(100).Key("50-99"),
                                            r => r.From(100).To(200).Key("100-199"),
                                            r => r.From(200).To(500).Key("200-499"),
                                            r => r.From(500).To(1000).Key("500-999"),
                                            r => r.From(1000).Key("1000或以上")
                              }.ToArray()))
                  ))))

                  ));
            var debug = response.DebugInformation;
            var a = response.Aggregations.ToList()[0].Value as Nest.SingleBucketAggregate;
            var bucketAgg = a.Aggregations.ToList()[0].Value as Nest.BucketAggregate;
            var keyedBucketsAgg = bucketAgg.Items.Cast<Nest.KeyedBucket<string>>().ToList();

            List<JObject> data = new List<JObject>();
            foreach (var keyedBucket in keyedBucketsAgg)
            {
                var keyedBucket_Key = keyedBucket.Key;
                var keyedBucket_Count = keyedBucket.DocCount;

                JObject dy = new JObject();
                dy.Add("城市", keyedBucket_Key);
                dy.Add("会议数量", keyedBucket_Count);

                foreach (var subKey in keyedBucket.Aggregations.Keys)
                {
                    switch (subKey)
                    {
                        case "JoinCount":
                            {
                                Nest.ValueAggregate joinCountAgg = keyedBucket.Aggregations["JoinCount"] as Nest.ValueAggregate;
                                if (joinCountAgg != null)
                                {
                                    dy["参会人数"] = joinCountAgg.Value;
                                }
                            }
                            break;
                        case "Days":
                            {
                                var daysAgg = keyedBucket.Aggregations["Days"] as Nest.BucketAggregate;
                                var dayItems = daysAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in dayItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Month":
                            {
                                var monthAgg = keyedBucket.Aggregations["Month"] as Nest.BucketAggregate;
                                var monthItems = monthAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in monthItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Scale":
                            {
                                var scaleAgg = keyedBucket.Aggregations["Scale"] as Nest.BucketAggregate;
                                var scaleItems = scaleAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in scaleItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                    }
                }

                data.Add(dy);
            }

            var c = response.Aggregations.ToList()[1].Value as Nest.SingleBucketAggregate;
            var bucketAgg1 = c.Aggregations.ToList()[0].Value as Nest.BucketAggregate;
            var keyedBucketsAgg1 = bucketAgg1.Items.Cast<Nest.KeyedBucket<string>>().ToList();

            foreach (var keyedBucket in keyedBucketsAgg1)
            {
                var keyedBucket_Key = keyedBucket.Key;
                var keyedBucket_Count = keyedBucket.DocCount;

                JObject dy = new JObject();
                dy.Add("城市", keyedBucket_Key);
                dy.Add("会议数量", keyedBucket_Count);

                foreach (var subKey in keyedBucket.Aggregations.Keys)
                {
                    switch (subKey)
                    {
                        case "JoinCount":
                            {
                                Nest.ValueAggregate joinCountAgg = keyedBucket.Aggregations["JoinCount"] as Nest.ValueAggregate;
                                if (joinCountAgg != null)
                                {
                                    dy["参会人数"] = joinCountAgg.Value;
                                }
                            }
                            break;
                        case "Days":
                            {
                                var daysAgg = keyedBucket.Aggregations["Days"] as Nest.BucketAggregate;
                                var dayItems = daysAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in dayItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Month":
                            {
                                var monthAgg = keyedBucket.Aggregations["Month"] as Nest.BucketAggregate;
                                var monthItems = monthAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in monthItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                        case "Scale":
                            {
                                var scaleAgg = keyedBucket.Aggregations["Scale"] as Nest.BucketAggregate;
                                var scaleItems = scaleAgg.Items.Cast<Nest.RangeBucket>().ToList();
                                foreach (var item in scaleItems)
                                {
                                    dy[item.Key] = item.DocCount;
                                }
                            }
                            break;
                    }
                }

                data.Add(dy);
            }

            using (ExcelPackage package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("sheet1");
                var colums = data[0].Properties().ToList();

                for (int j = 1; j <= colums.Count; j++)
                {
                    worksheet.Cells[1, j].Style.Fill.PatternType = ExcelFillStyle.Gray125;
                    //worksheet.Cells[1, j].Style.ShrinkToFit = true;
                    worksheet.Cells[1, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells[1, j].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
                    worksheet.Cells[1, j].Style.Font.Size = 16;
                    worksheet.Cells[1, j].Style.Font.Bold = true;
                    worksheet.Cells[1, j].Value = colums[j - 1].Name;
                }

                for (int i = 1; i <= data.Count; i++)
                {
                    var row = data[i - 1];

                    for (int j = 1; j <= colums.Count; j++)
                    {
                        worksheet.Cells[i + 1, j].Value = row[colums[j - 1].Name].ToString();
                    }
                }

                if (File.Exists("d:\\789.xlsx"))
                {
                    File.Delete("d:\\789.xlsx");
                }

                using (FileStream stream = new FileStream("d:\\789.xlsx", FileMode.Create, FileAccess.ReadWrite))
                {
                    package.SaveAs(stream);
                }
            }
        }

        static void AggregationTest1()
        {
            ElasticHub<HYEvent> hub = new ElasticHub<HYEvent>("hexindb");
            var response = hub.Client.Search<HYEvent>(search => search.Aggregations(agg => agg.
            Filter("thisyear", t => t.Filter(f => f.
               DateRange(dr => dr.Field("DateFrom").
                   GreaterThanOrEquals(new DateTime(2016, 1, 1, 0, 0, 0)))
             ).Aggregations(af => af.Terms("EventSubType", st => st.Field("EventSubType").Size(100).
                      Aggregations(
                              a1 => a1.Terms("IsFree", a1f => a1f.Field("IsFree"))
                  ))))));
            var debug = response.DebugInformation;
            var a = response.Aggregations.ToList()[0].Value as Nest.SingleBucketAggregate;
            var bucketAgg = a.Aggregations.ToList()[0].Value as Nest.BucketAggregate;
            var keyedBucketsAgg = bucketAgg.Items.Cast<Nest.KeyedBucket<dynamic>>().ToList();

            List<Dictionary<string, object>> data = new List<Dictionary<string, object>>();
            foreach (var keyedBucket in keyedBucketsAgg)
            {
                var keyedBucket_Key = keyedBucket.Key;
                var keyedBucket_Count = keyedBucket.DocCount;

                Dictionary<string, object> dy = new Dictionary<string, object>();
                var desc = Enum.Parse(typeof(EventSubType), keyedBucket_Key.ToString()).ToString();
                dy.Add("会议类型", desc);
                dy.Add("会议数量", keyedBucket_Count);

                foreach (var subKey in keyedBucket.Aggregations.Keys)
                {
                    switch (subKey)
                    {
                        case "IsFree":
                            {
                                Nest.BucketAggregate isFreeAgg = keyedBucket.Aggregations["IsFree"] as Nest.BucketAggregate;
                                if (isFreeAgg != null)
                                {
                                    var isFreeItems = isFreeAgg.Items.Cast<Nest.KeyedBucket<dynamic>>().ToList();
                                    foreach (var isFreeItem in isFreeItems)
                                    {
                                        if (isFreeItem.Key.ToString() == "1")
                                        {
                                            dy["免费"] = isFreeItem.DocCount;
                                            continue;
                                        }

                                        if (isFreeItem.Key.ToString() == "0")
                                        {
                                            dy["收费"] = isFreeItem.DocCount;
                                            continue;
                                        }
                                    }
                                }
                            }
                            break;
                    }
                }

                data.Add(dy);
            }

            using (ExcelPackage package = new ExcelPackage())
            {
                var worksheet = package.Workbook.Worksheets.Add("sheet1");
                var colums = data[0].Keys.ToList();

                for (int j = 1; j <= colums.Count; j++)
                {
                    worksheet.Cells[1, j].Style.Fill.PatternType = ExcelFillStyle.Gray125;
                    //worksheet.Cells[1, j].Style.ShrinkToFit = true;
                    worksheet.Cells[1, j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells[1, j].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
                    worksheet.Cells[1, j].Style.Font.Size = 16;
                    worksheet.Cells[1, j].Style.Font.Bold = true;
                    worksheet.Cells[1, j].Value = colums[j - 1];
                }

                for (int i = 1; i <= data.Count; i++)
                {
                    var row = data[i - 1];

                    for (int j = 1; j <= colums.Count; j++)
                    {
                        worksheet.Cells[i + 1, j].Value = row[colums[j - 1]];
                    }
                }

                if (File.Exists("d:\\123.xlsx"))
                {
                    File.Delete("d:\\123.xlsx");
                }

                using (FileStream stream = new FileStream("d:\\123.xlsx", FileMode.Create, FileAccess.ReadWrite))
                {
                    package.SaveAs(stream);
                }
            }
        }


        static void SerializeTest()
        {
            Contact contact = new Contact();
            contact.RealName = "haha";
            contact.Mobile = "18659455796";
            contact.ExtraFieldsForES = new Dictionary<string, object>();
            contact.ExtraFieldsForES.Add("222", "fdfdf");
            contact.ExtraFieldsForES.Add("333", new List<object>() { "qwe", "dsd", 123, "456" });
            contact.ExtraFieldsForMongo = new MongoDB.Bson.BsonDocument();
            contact.ExtraFieldsForMongo.Add("123", "43gdg");
            var json = Newtonsoft.Json.JsonConvert.SerializeObject(contact);

            var contact1 = Newtonsoft.Json.JsonConvert.DeserializeObject<Contact>(json);
            var json1 = Newtonsoft.Json.JsonConvert.SerializeObject(contact1);


            var s = "";
        }

        static void JoinHistoryTest()
        {
            JoinUser user = new JoinUser()
            {
                UserId = 111111,
                UserName = "aa",
                JoinHistories = new List<JoinHistory>()
                {
                    new JoinHistory(){EventId=112233, JoinTime= DateTime.Now.AddMonths(-7), Signed=true  },
                    new JoinHistory(){EventId=223344, JoinTime= DateTime.Now.AddMonths(-8), Signed=true  },
                    new JoinHistory(){EventId=334455, JoinTime= DateTime.Now.AddMonths(-4), Signed=true  }
                }
            };

            JoinUser user1 = new JoinUser()
            {
                UserId = 222222,
                UserName = "bb",
                JoinHistories = new List<JoinHistory>()
                {
                    new JoinHistory(){EventId=232323, JoinTime= DateTime.Now.AddDays(-300), Signed=true  },
                    new JoinHistory(){EventId=343434, JoinTime= DateTime.Now.AddMonths(-250), Signed=true  },
                    new JoinHistory(){EventId=454545, JoinTime= DateTime.Now.AddMonths(-260), Signed=true  }
                }
            };

            JoinUser user2 = new JoinUser()
            {
                UserId = 333333,
                UserName = "cc",
                JoinHistories = new List<JoinHistory>()
                {
                    new JoinHistory(){EventId=556677, JoinTime= DateTime.Now.AddDays(-20), Signed=true  },
                    new JoinHistory(){EventId=667788, JoinTime= DateTime.Now.AddDays(-25), Signed=true  },
                    new JoinHistory(){EventId=889900, JoinTime= DateTime.Now.AddDays(-30), Signed=true  }
                }
            };

            ElasticHub<JoinUser> hub = new ElasticHub<JoinUser>("demo");
            var response = hub.Client.CreateIndex(hub.Index);
            var mapResponse = hub.Client.Map<JoinUser>(m => m.AutoMap().DateDetection(false));
            var a = hub.Client.Index<JoinUser>(user);
            var b = hub.Client.Index<JoinUser>(user1);
            var c = hub.Client.Index<JoinUser>(user2);
        }
    }
}
